<?php 
$sql = <<<SQL
DROP TABLE IF EXISTS user;
CREATE TABLE user (
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	login VARCHAR(100) NOT NULL,
	password VARCHAR(32) NOT NULL,
	salt VARCHAR(5) NOT NULL,
	email VARCHAR(255) NOT NULL DEFAULT '',
	firstname VARCHAR(255) NOT NULL DEFAULT '',
	lastname VARCHAR(255) NOT NULL DEFAULT '',
	role ENUM('member', 'admin') DEFAULT 'member',
	is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
   primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS article;
CREATE TABLE article (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	title VARCHAR(255) NOT NULL DEFAULT '',
	description text NOT NULL,
	content text NOT NULL,
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	category_id SMALLINT UNSIGNED NOT NULL,
	primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS tag;
CREATE TABLE tag (
	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL DEFAULT '',
	UNIQUE KEY name_unique(name),
	primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS article_to_tag;
CREATE TABLE article_to_tag (
	article_id INT UNSIGNED NOT NULL,
	tag_id MEDIUMINT UNSIGNED NOT NULL,
	primary key (article_id, tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS category;
CREATE TABLE category (
	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL DEFAULT '',
	UNIQUE KEY name_unique(name),
	primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS country;
CREATE TABLE country (
	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL DEFAULT '',
	UNIQUE KEY name_unique(name),
	primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS image;
CREATE TABLE image (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	filename VARCHAR(255) NOT NULL DEFAULT '',
	path VARCHAR(255) NOT NULL DEFAULT '',
	title VARCHAR(255) NOT NULL DEFAULT '',
	description text NOT NULL,
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE KEY place_unique(path, filename),
	primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS image_to_article;
CREATE TABLE image_to_article (
	image_id INT UNSIGNED NOT NULL,
	article_id INT UNSIGNED NOT NULL,
	primary key (image_id, article_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO user (login, password, role, is_active) VALUES
	('admin', '202cb962ac59075b964b07152d234b70', 'admin', 1)
SQL;
return $sql;